package com.fone.player.storage;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteStatement;

import com.fone.player.entity.NotificationBean;
import com.fone.player.util.L;

/**
 * 
 * 通知表适配器
 * 
 * @author wangyang
 * @since 2014年4月29日
 */
class NotificationDataBaseAdapter implements IFoneDatabase {
	private static final String TAG = NotificationDataBaseAdapter.class
			.getSimpleName();
	private static final NotificationDataBaseAdapter mInstance = new NotificationDataBaseAdapter();
	private OperateDataBaseTemplate mGeneralDataBaseTemplate = OperateDataBaseTemplate
			.getInstance();

	private NotificationDataBaseAdapter() {

	}

	static NotificationDataBaseAdapter getInstance() {
		return mInstance;
	}

	/**
	 * 
	 * 添加通知集合
	 * 
	 * @param notificationList
	 *            通知对象集合
	 * @return int 插入结果<br>
	 *         1 插入成功 <br>
	 *         -1 插入异常
	 * 
	 */
	public int addNotificationList(List<NotificationBean> notificationList) {
		synchronized (TAG) {

			// 创建sql语句
			StringBuilder sql = new StringBuilder();
			sql.append("insert into ").append(TB_NOTIFICATION).append(" (");
			sql.append(NOTIFICATION_SERVER_ID).append(",");
			sql.append(NOTIFICATION_NAME).append(",");
			sql.append(NOTIFICATION_CONTENT).append(",");
			sql.append(NOTIFICATION_DRAMA_NAME).append(",");
			sql.append(NOTIFICATION_TYPE).append(",");
			sql.append(NOTIFICATION_CONTENT_TYPE).append(",");
			sql.append(NOTIFICATION_CONTENT_PATTERN_TYPE).append(",");
			sql.append(NOTIFICATION_SHOW_TYPE).append(",");
			sql.append(NOTIFICATION_PLAY_TYPE).append(",");
			sql.append(NOTIFICATION_EXTERNAL_TYPE).append(",");
			sql.append(NOTIFICATION_DEFINITION_TYPE).append(",");
			sql.append(NOTIFICATION_IMAGE_URL).append(",");
			sql.append(NOTIFICATION_ICON_URL).append(",");
			sql.append(NOTIFICATION_EXTERNAL_URL).append(",");
			sql.append(NOTIFICATION_URL).append(",");
			sql.append(NOTIFICATION_SHARE_URL).append(",");
			sql.append(NOTIFICATION_CREATE_TIME).append(",");
			sql.append(NOTIFICATION_COMMENT_REPLY_TIME).append(",");
			sql.append(NOTIFICATION_FEED_BACK_TIME).append(",");
			sql.append(NOTIFICATION_FEED_BACK_DESC).append(",");
			sql.append(NOTIFICATION_IS_READ).append(",");
			sql.append(NOTIFICATION_IS_SHOW).append(",");
			sql.append(NOTIFICATION_IS_SHOW_PLAY_BUTTON).append(",");
			sql.append(NOTIFICATION_IS_SHOW_DOWNLOAD_BUTTON);
			sql.append(") values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);");

			try {
				mGeneralDataBaseTemplate.open();
				mGeneralDataBaseTemplate.beginTransaction();

				SQLiteStatement fileStatement = mGeneralDataBaseTemplate
						.getSQLiteStatement(sql.toString());

				for (NotificationBean notificationBean : notificationList) {
					fileStatement.bindString(1,
							notificationBean.getNotificationServerId());
					fileStatement.bindString(2,
							notificationBean.getNotificationName());
					fileStatement.bindString(3,
							notificationBean.getNotificationContent());
					fileStatement.bindString(4,
							notificationBean.getNotificationDramaName());
					fileStatement.bindLong(5,
							notificationBean.getNotificationType());
					fileStatement.bindLong(6,
							notificationBean.getNotificationContentType());
					fileStatement.bindLong(7, notificationBean
							.getNotificationContentPatternType());
					fileStatement.bindLong(8,
							notificationBean.getNotificationShowType());
					fileStatement.bindLong(9,
							notificationBean.getNotificationPlayType());
					fileStatement.bindLong(10,
							notificationBean.getNotificationExternalType());
					fileStatement.bindString(11,
							notificationBean.getNotificationDefinitionType());
					fileStatement.bindString(12,
							notificationBean.getNotificationImageUrl());
					fileStatement.bindString(13,
							notificationBean.getNotificationIconUrl());
					fileStatement.bindString(14,
							notificationBean.getNotificationExternalUrl());
					fileStatement.bindString(15,
							notificationBean.getNotificationUrl());
					fileStatement.bindString(16,
							notificationBean.getNotificationShareUrl());
					fileStatement.bindLong(17,
							notificationBean.getNotificationCreateTime());
					fileStatement.bindString(18,
							notificationBean.getNotificationCommentReplyTime());
					fileStatement.bindString(19,
							notificationBean.getNotificationFeedBackTime());
					fileStatement.bindString(20,
							notificationBean.getNotificationFeedBackDesc());
					fileStatement.bindLong(21,
							notificationBean.getNotificationIsRead() ? 1 : 0);
					fileStatement.bindLong(22,
							notificationBean.getNotificationIsShow() ? 1 : 0);
					fileStatement.bindLong(23, notificationBean
							.getNotificationIsShowPlayButton() ? 1 : 0);
					fileStatement.bindLong(24, notificationBean
							.getNotificationIsShowDownloadButton() ? 1 : 0);

					mGeneralDataBaseTemplate.insert(fileStatement);
				}

				mGeneralDataBaseTemplate.setTransactionSuccessful();

			} catch (Exception e) {
				if (e != null) {
					e.printStackTrace();
					L.v(TAG, e.getMessage());
				}
				return -1;
			} finally {
				mGeneralDataBaseTemplate.endTransaction();
				mGeneralDataBaseTemplate.close();
			}
			return 1;
		}
	}

	/**
	 * 
	 * 根据展示状态获取所有消息
	 * 
	 * @param isShow
	 *            是否展示
	 * @return ArrayList<NotificationBean> 通知集合
	 */
	public ArrayList<NotificationBean> getNotificationListByIsShow(
			boolean isShow) {
		StringBuilder sql = new StringBuilder("select * from ");
		sql.append(TB_NOTIFICATION);
		sql.append(" where ");
		sql.append(NOTIFICATION_IS_SHOW);
		sql.append("=");
		sql.append(isShow ? 1 : 0);
		ArrayList<NotificationBean> notificationList = null;
		Cursor cursor = null;
		try {
			mGeneralDataBaseTemplate.open(true);
			cursor = mGeneralDataBaseTemplate.select(sql.toString());

			if (cursor == null) {
				return null;
			}

			// 读取数据
			notificationList = new ArrayList<NotificationBean>();
			while (cursor.moveToNext()) {
				NotificationBean notification = getNotificationBeanByCursor(cursor);
				notificationList.add(notification);
			}

		} catch (Exception e) {
			L.e(TAG, "getNotificationList()",
					e == null ? "e is null" : e.getMessage());
		} finally {
			if (cursor != null) {

				cursor.close();

			}
			mGeneralDataBaseTemplate.close();

		}
		return notificationList;
	}

	/**
	 * 
	 * 根据服务端通知ID查询通知对象
	 * 
	 * @param notificationServerId
	 *            服务器段通知ID
	 * @return NotificationBean 通知对象
	 */
	public NotificationBean getNotificationByServerId(
			String notificationServerId) {
		StringBuilder sql = new StringBuilder();
		sql.append("select * from ");
		sql.append(TB_NOTIFICATION);
		sql.append(" where ");
		sql.append(NOTIFICATION_SERVER_ID).append("='")
				.append(notificationServerId).append("'");

		NotificationBean notification = null;
		Cursor cursor = null;
		try {
			mGeneralDataBaseTemplate.open();
			cursor = mGeneralDataBaseTemplate.select(sql.toString());

			if (cursor == null) {
				return null;
			}

			// 读取数据
			while (cursor.moveToNext()) {
				notification = getNotificationBeanByCursor(cursor);
			}

		} catch (Exception e) {
			L.e(TAG, "getNotificationByServerId()",
					e == null ? "e is null" : e.getMessage());
		} finally {
			if (cursor != null) {

				cursor.close();

			}
			mGeneralDataBaseTemplate.close();

		}
		return notification;
	}

	/**
	 * 
	 * 按时间排序,根据消息类型分页查询所有消息
	 * 
	 * @param notificationType
	 * @param pageNum
	 * @param pageSize
	 * @return ArrayList<NotificationBean> 通知集合
	 */
	public ArrayList<NotificationBean> getNotificationList(boolean isNew,
			int notificationType, int notificationId, int pageSize) {
		StringBuilder sql = new StringBuilder();
		if (isNew) {
			// 第一次加载（下拉刷新）
			if (notificationId == 0) {
				sql.append("select * from ");
				sql.append(TB_NOTIFICATION);
				sql.append(" where ");
				sql.append(NOTIFICATION_TYPE).append("=")
						.append(notificationType);
				sql.append(" and ");
				sql.append(NOTIFICATION_ID);
				sql.append("<=(");
				sql.append("select max(").append(NOTIFICATION_ID)
						.append(") from ");
				sql.append(TB_NOTIFICATION);
				sql.append(" where ");
				sql.append(NOTIFICATION_TYPE).append("=")
						.append(notificationType);
				sql.append(")");
				sql.append(" order by ");
				sql.append(NOTIFICATION_ID);
				sql.append(" desc");
				sql.append(" limit ");
				// 要显示多少条记录
				sql.append(pageSize);
				sql.append(" offset ");
				// 跳过多少条记录
				sql.append(0);
			} else {
				// 第二次加载（下拉刷新）
				sql.append("select * from ");
				sql.append(TB_NOTIFICATION);
				sql.append(" where ");
				sql.append(NOTIFICATION_TYPE).append("=")
						.append(notificationType);
				sql.append(" and ");
				sql.append(NOTIFICATION_ID);
				sql.append(">");
				sql.append(notificationId);
				sql.append(" order by ");
				sql.append(NOTIFICATION_ID);
				sql.append(" desc");
				sql.append(" limit ");
				// 要显示多少条记录
				sql.append(pageSize);
				sql.append(" offset ");
				// 跳过多少条记录
				sql.append(0);
			}

		} else {
			// 加载（上拉刷新）
			sql.append("select * from ");
			sql.append(TB_NOTIFICATION);
			sql.append(" where ");
			sql.append(NOTIFICATION_TYPE).append("=").append(notificationType);
			sql.append(" and ");
			sql.append(NOTIFICATION_ID);
			sql.append("<");
			sql.append(notificationId);
			sql.append(" order by ");
			sql.append(NOTIFICATION_ID);
			sql.append(" desc");
			sql.append(" limit ");
			// 要显示多少条记录
			sql.append(pageSize);
			sql.append(" offset ");
			// 跳过多少条记录
			sql.append(0);
		}
		ArrayList<NotificationBean> notificationList = new ArrayList<NotificationBean>();
		Cursor cursor = null;
		try {
			mGeneralDataBaseTemplate.open();
			cursor = mGeneralDataBaseTemplate.select(sql.toString());

			if (cursor == null) {
				return null;
			}

			// 读取数据
			while (cursor.moveToNext()) {
				NotificationBean notification = getNotificationBeanByCursor(cursor);
				notificationList.add(notification);
			}

		} catch (Exception e) {
			L.e(TAG, "getNotificationList()",
					e == null ? "e is null" : e.getMessage());
		} finally {
			if (cursor != null) {

				cursor.close();

			}
			mGeneralDataBaseTemplate.close();

		}
		return notificationList;
	}

	/**
	 * 根据通知对象更新消息读取状态
	 * 
	 * @param notificationBean
	 *            通知对象
	 * @return int 操作结果<br>
	 *         1 更新成功<br>
	 *         -1 更新异常<br>
	 */
	public int updateNotificationIsRead(NotificationBean notificationBean) {
		int result = 1;
		try {

			StringBuilder where = new StringBuilder();
			where.append(NOTIFICATION_ID);
			where.append("=?");
			ContentValues contentValues = new ContentValues();
			contentValues.put(NOTIFICATION_IS_READ,
					notificationBean.getNotificationIsRead() ? 1 : 0);
			mGeneralDataBaseTemplate.open();
			int row = mGeneralDataBaseTemplate.update(TB_NOTIFICATION,
					contentValues, where.toString(), new String[] { String
							.valueOf(notificationBean.getNotificationId()) });
			result = row > 0 ? 1 : -1;

		} catch (Exception e) {
			result = -1;
		} finally {
			mGeneralDataBaseTemplate.close();
		}
		return result;
	}

	/**
	 * 更新消息展示状态
	 * 
	 * @param notificationBeanList
	 *            通知对象集合
	 * @return int 操作结果<br>
	 *         1 更新成功<br>
	 *         -1 更新异常<br>
	 */
	public int updateNotificationIsShow(
			List<NotificationBean> notificationBeanList) {
		int result = 1;
		try {

			StringBuilder where = new StringBuilder();
			where.append(NOTIFICATION_ID);
			where.append("=?");

			for (NotificationBean notificationBean : notificationBeanList) {
				ContentValues contentValues = new ContentValues();
				contentValues.put(NOTIFICATION_IS_READ,
						notificationBean.getNotificationIsRead() ? 1 : 0);
				mGeneralDataBaseTemplate.open();
				int row = mGeneralDataBaseTemplate.update(TB_NOTIFICATION,
						contentValues, where.toString(),
						new String[] { String.valueOf(notificationBean
								.getNotificationId()) });
				result = row > 0 ? 1 : -1;

				if (result == -1) {
					break;
				}
			}

		} catch (Exception e) {
			result = -1;
		} finally {
			mGeneralDataBaseTemplate.close();
		}
		return result;
	}

	/**
	 * 
	 * 根据消息标题删除消息 <br>
	 * 同步方法
	 * 
	 * @param notificationName
	 *            消息标题
	 * @return int 操作结果<br>
	 *         1 删除成功<br>
	 *         -1 删除异常<br>
	 */
	public int deleteNotificationById(
			List<NotificationBean> notificationBeanList) {
		try {

			// 删除本地数据库

			mGeneralDataBaseTemplate.open();
			StringBuilder where = new StringBuilder();
			where.append(NOTIFICATION_ID);
			where.append("=?");
			for (NotificationBean notificationBean : notificationBeanList) {
				mGeneralDataBaseTemplate.delete(TB_NOTIFICATION, where
						.toString(), new String[] { String
						.valueOf(notificationBean.getNotificationId()) });
			}

			return 1;

		} catch (Exception e) {
			e.printStackTrace();
			L.e(TAG, "deleteNotificationByNotificationName", e.getMessage());
			return -1;
		} finally {
			mGeneralDataBaseTemplate.close();
		}

	}

	private NotificationBean getNotificationBeanByCursor(Cursor cursor) {
		NotificationBean notification = new NotificationBean();
		notification.setNotificationId(cursor.getInt(cursor
				.getColumnIndex(NOTIFICATION_ID)));
		notification.setNotificationServerId(cursor.getString(cursor
				.getColumnIndex(NOTIFICATION_SERVER_ID)));
		notification.setNotificationName(cursor.getString(cursor
				.getColumnIndex(NOTIFICATION_NAME)));
		notification.setNotificationContent(cursor.getString(cursor
				.getColumnIndex(NOTIFICATION_CONTENT)));
		notification.setNotificationDramaName(cursor.getString(cursor
				.getColumnIndex(NOTIFICATION_DRAMA_NAME)));
		notification.setNotificationType(cursor.getInt(cursor
				.getColumnIndex(NOTIFICATION_TYPE)));
		notification.setNotificationContentType(cursor.getInt(cursor
				.getColumnIndex(NOTIFICATION_CONTENT_TYPE)));
		notification.setNotificationContentPatternType(cursor.getInt(cursor
				.getColumnIndex(NOTIFICATION_CONTENT_PATTERN_TYPE)));
		notification.setNotificationShowType(cursor.getInt(cursor
				.getColumnIndex(NOTIFICATION_SHOW_TYPE)));
		notification.setNotificationPlayType(cursor.getInt(cursor
				.getColumnIndex(NOTIFICATION_PLAY_TYPE)));
		notification.setNotificationExternalType(cursor.getInt(cursor
				.getColumnIndex(NOTIFICATION_EXTERNAL_TYPE)));
		notification.setNotificationDefinitionType(cursor.getString(cursor
				.getColumnIndex(NOTIFICATION_DEFINITION_TYPE)));
		notification.setNotificationImageUrl(cursor.getString(cursor
				.getColumnIndex(NOTIFICATION_IMAGE_URL)));
		notification.setNotificationIconUrl(cursor.getString(cursor
				.getColumnIndex(NOTIFICATION_ICON_URL)));
		notification.setNotificationExternalUrl(cursor.getString(cursor
				.getColumnIndex(NOTIFICATION_EXTERNAL_URL)));
		notification.setNotificationUrl(cursor.getString(cursor
				.getColumnIndex(NOTIFICATION_URL)));
		notification.setNotificationShareUrl(cursor.getString(cursor
				.getColumnIndex(NOTIFICATION_SHARE_URL)));
		notification.setNotificationCreateTime(cursor.getLong(cursor
				.getColumnIndex(NOTIFICATION_CREATE_TIME)));
		notification.setNotificationCommentReplyTime(cursor.getString(cursor
				.getColumnIndex(NOTIFICATION_COMMENT_REPLY_TIME)));
		notification.setNotificationFeedBackTime(cursor.getString(cursor
				.getColumnIndex(NOTIFICATION_FEED_BACK_TIME)));
		notification.setNotificationFeedBackDesc(cursor.getString(cursor
				.getColumnIndex(NOTIFICATION_FEED_BACK_DESC)));
		notification.setNotificationIsRead(cursor.getInt(cursor
				.getColumnIndex(NOTIFICATION_IS_READ)) == 1);
		notification.setNotificationIsShow(cursor.getInt(cursor
				.getColumnIndex(NOTIFICATION_IS_SHOW)) == 1);
		notification.setNotificationIsShowPlayButton(cursor.getInt(cursor
				.getColumnIndex(NOTIFICATION_IS_SHOW_PLAY_BUTTON)) == 1);
		notification.setNotificationIsShowDownloadButton(cursor.getInt(cursor
				.getColumnIndex(NOTIFICATION_IS_SHOW_DOWNLOAD_BUTTON)) == 1);
		return notification;
	}
}
